const express = require("express");
const app = express();
app.listen(3000,()=>{
  console.log("服务器已开启");
});
const path = require("path");
//加载db.js(mysql数据库的链接对象)
const db = require("./db.js");
//加载模板引擎的模块 express-art-template
const template = require("express-art-template");
//配置模板引擎
app.engine("html",template);
//加载body-parser模块
const bp = require("body-parser");
//将body-parser模块注册为中间件
app.use(bp.urlencoded({extends:false}));
//监听/index路由
app.get("/index",(req,res)=>{
  //执行slq语句
  const sql = `select * from student
                join dept on student.sdept = dept.did
                order by student.sno asc`;
  db.query(sql,(err,result)=>{
    //执行sql语句失败 
    if(err){
      console.log(err);
      return res.render(path.join(__dirname,"./view/index.html"),{list:[]})
    };
    //执行sql语句成功 将结果渲染到页面
    console.log(result)
    res.render(path.join(__dirname,"./view/index.html"),{list:result})
  });
});
//监听/add路由
app.get('/add',(req,res)=>{
  res.sendFile(path.join(__dirname,"./view/add.html"));
});
//因为表单是post方式提交，所以是post请求，需要用post方法监听
app.post("/addstu",(req,res)=>{
  //1.接收表单提交的数据(需要重构)
  const obj ={
    sname:req.body.name,
    sage:req.body.age,
    sgender:req.body.gender,
    stime:req.body.time,
    sdept:req.body.dept,
  };
  const sql = "insert into student set ?"
  db.query(sql,obj,(err,result)=>{
    if(err || result.affectedRows === 0){
      console.log(err);
      //当添加失败时，直接跳转回，/add地址
      return res.redirect("/add");
    }
    console.log(result);
    //redirect是express提供的方法，用来跳转到其他路由地址
    res.redirect("/index")
  });
});
//监听/delstu
app.get('/delstu',(req,res)=>{
  // console.log(req.query.no)
  const sql = `delete from student where sno = ?`
  db.query(sql,req.query.no,(err,result)=>{
    if(err || result.affectedRows === 0){
      console.log(err);
    };
    res.redirect("/index");
  });
});
//监听/editstu (显示edit.html页面)
app.get('/editstu',(req,res)=>{
  // console.log(req.query);
  //编写sql语句-- 根据学号查询学生的详细信息
  const sql = `select * from student where sno=?`;
  //执行sql语句
  db.query(sql,req.query.no,(err,result)=>{
    // console.log(result);
    //result仍然是一个数组，数组中只有一个单元
    //处理sql执行结果--使用模板引擎将result中的数据显示到edit.html页面上
    //result[0] = {sno:1,sname:"admin",sage:20,...}
    res.render(path.join(__dirname,'./view/edit.html'),result[0])
  });
});
//监听/modifystu()
app.post('/modifystu',(req,res)=>{
  console.log(req.body);
  const sql = `update student set ? where sno=?`;
  const obj = {
    sname:req.body.name,
    sage:req.body.age,
    sgender:req.body.gender,
    stime:req.body.time,
    sdept:req.body.dept
  }
  db.query(sql,[obj,req.body.no],(err,result)=>{
    if(err){
      console.log(err);
      return res.redirect('/editstu?no='+ req.body.no);
    };
    res.redirect('/index');
  });
});



// const express = require("express");
// const app = express();
// app.listen(3000,()=>{
//   console.log("服务器已开启");
// });
// const path = require("path");
// const db = require("./db.js");
// const template = require("express-art-template");
// app.engine("html",template);
// app.get("/index",(req,res)=>{
//   const sql = `select * from student
//                 join dept on student.sdept = dept.did
//                 order by student.sno asc`;
//   db.query(sql,(err,result)=>{
//     if(err){
//       console.log(err);
//       res.render(path.join(__dirname,"./view/index.html"),{list:[]});
//     };
//     console.log(result);
//     res.render(path.join(__dirname,"./view/index.html"),{list:result})
//   });
// });

// app.get('/add',(req,res)=>{
//   res.sendFile(path.join(__dirname,"./view/add.html"))
// });
// const bp = require("body-parser");
// app.use(bp.urlencoded({extends:false}));
// app.post('/addstu',(req,res)=>{
//   console.log(req.body);
//   const sql = `insert into student set ?`;
//   const obj = {
//     sname:req.body.name,
//     sage:req.body.age,
//     sgender:req.body.gender,
//     stime:req.body.time,
//     sdept:req.body.dept
//   };
//   db.query(sql,obj,(err,result)=>{
//     if(err ||  result.affectedRows === 0){
//       console.log(err);
//       return res.redirect('/add')
//     };
//     res.redirect('/index')
//   });
// });